package com.mystnihon.freeremote.db;

import java.util.ArrayList;

import android.content.ContentResolver;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.v4.content.CursorLoader;

import com.mystnihon.freeremote.db.table.ChannelTable;
import com.mystnihon.freeremote.db.table.ProgramTable;
import com.mystnihon.freeremote.db.table.SeedboxTable;

public class DBHelper extends SQLiteOpenHelper {
	private static final int DATABASE_VERSION = 2;
	private static final String DATABASE_NAME = "dictionary";
	private static final long MINUS_7_DAYS = 3600l * 24l * 7l;

	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(SeedboxTable.getCreateStatement());
		db.execSQL(ChannelTable.getCreateStatement());
		db.execSQL(ProgramTable.getCreateStatement());

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + SeedboxTable.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + ChannelTable.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + ProgramTable.TABLE_NAME);
		onCreate(db);

	}

	public static final int deleteSeedbox(ContentResolver cr, int id) {
		return cr.delete(SeedboxTable.CONTENT_URI, SeedboxTable._ID + "=?", new String[] { String.valueOf(id) });
	}

	public static final ArrayList<SeedboxTable> getListOfSeedbox(ContentResolver cr) {
		ArrayList<SeedboxTable> list = new ArrayList<SeedboxTable>();

		Cursor c = cr.query(SeedboxTable.CONTENT_URI, SeedboxTable.CONTENT_PROJECTION, null, null, null);
		while (c.moveToNext()) {
			SeedboxTable sdbTable = SeedboxTable.inflateObject(c);
			list.add(sdbTable);
		}
		c.close();
		return list;
	}

	public static final void insertSeedbox(ContentResolver cr, SeedboxTable seedbox) {
		cr.insert(SeedboxTable.CONTENT_URI, seedbox.getContentValues());
	}

	public static final void insertOrUpdateChannel(ContentResolver cr, ChannelTable channel) {
		final Cursor c = cr.query(ChannelTable.CONTENT_URI, ChannelTable.CONTENT_PROJECTION_SMALL, ChannelTable.COLUMN_CHANNEL_ID + "=?", new String[] { String.valueOf(channel.mChannelId) }, null);
		if (c.moveToNext()) {
			cr.update(ChannelTable.CONTENT_URI, channel.getContentValues(), ChannelTable.COLUMN_CHANNEL_ID + "=?", new String[] { String.valueOf(channel.mChannelId) });
		} else {
			cr.insert(ChannelTable.CONTENT_URI, channel.getContentValues());
		}
		c.close();
	}

	public static final void cleanProgramTable(ContentResolver cr, long currentTime) {
		cr.delete(ProgramTable.CONTENT_URI, ProgramTable.COLUMN_ENDTIME + "<?", new String[] { String.valueOf(currentTime - MINUS_7_DAYS) });
	}

	public static final void insertOrUpdateProgram(ContentResolver cr, ProgramTable program) {
		final Cursor c = cr.query(ProgramTable.CONTENT_URI, ProgramTable.CONTENT_PROJECTION_SMALL, ProgramTable.COLUMN_PROGRAM_ID + "=?", new String[] { String.valueOf(program.mProgramId) }, null);
		if (c.moveToNext()) {
			cr.update(ProgramTable.CONTENT_URI, program.getContentValues(), ProgramTable.COLUMN_PROGRAM_ID + "=?", new String[] { String.valueOf(program.mProgramId) });
		} else {
			cr.insert(ProgramTable.CONTENT_URI, program.getContentValues());
		}
		c.close();
	}

	public static final ArrayList<ChannelTable> getListOfChannel(ContentResolver cr) {
		ArrayList<ChannelTable> list = new ArrayList<ChannelTable>();

		Cursor c = cr.query(ChannelTable.CONTENT_URI, ChannelTable.CONTENT_PROJECTION, null, null, null);
		while (c.moveToNext()) {
			ChannelTable sdbTable = ChannelTable.inflateObject(c);
			list.add(sdbTable);
		}
		c.close();
		return list;
	}

	public static final CursorLoader getListOfChannel(Context ct) {
		return new CursorLoader(ct, ChannelTable.CONTENT_URI, ChannelTable.CONTENT_PROJECTION, null, null, ChannelTable.COLUMN_CHANNEL_NUMBER + " ASC");
	}

	public static final CursorLoader getCurrentProgramsChannelCursorLoader(Context context, long currentTime) {
		final String timestamp = String.valueOf(currentTime);
		final StringBuffer sb = new StringBuffer("SELECT DISTINCT " + ProgramTable.TABLE_NAME + ".*,channel_main.* FROM " + ProgramTable.TABLE_NAME + " INNER JOIN " + ChannelTable.TABLE_NAME
				+ " AS channel_main ON " + ProgramTable.TABLE_NAME + "." + ProgramTable.COLUMN_CHANNEL_ID + "=channel_main." + ChannelTable.COLUMN_CHANNEL_ID);
		sb.append(" WHERE ");
		sb.append(ProgramTable.COLUMN_STARTTIME);
		sb.append(" < ? AND ");
		sb.append(ProgramTable.COLUMN_ENDTIME);
		sb.append(" > ? ");
		sb.append(" ORDER BY ");
		sb.append("channel_main");
		sb.append(".");
		sb.append(ChannelTable.COLUMN_CHANNEL_NUMBER + " ASC");

		return new CursorLoader(context, DBProvider.RAW_QUERY_URI, null, sb.toString(), new String[] { timestamp, timestamp }, null);

	}
//	public static final CursorLoader getCurrentProgramsChannelCursorLoader(Context context, long currentTime) {
//		final String timestamp = String.valueOf(currentTime);
//		final StringBuffer sb = new StringBuffer("SELECT DISTINCT " + ProgramTable.TABLE_NAME + ".*,channel_main.* FROM " + ProgramTable.TABLE_NAME + " INNER JOIN " + ChannelTable.TABLE_NAME
//				+ " AS channel_main ON " + ProgramTable.TABLE_NAME + "." + ProgramTable.COLUMN_CHANNEL_ID + "=channel_main." + ChannelTable.COLUMN_CHANNEL_ID);
//		sb.append(" ORDER BY ");
//		sb.append("channel_main");
//		sb.append(".");
//		sb.append(ChannelTable.COLUMN_CHANNEL_NUMBER + " ASC");
//
//		return new CursorLoader(context, DBProvider.RAW_QUERY_URI, null, sb.toString(), null, null);
//
//	}

	public static class ChanProg {
		public static final int MARGE = 10;

		public int mProgChannelId;
		public long mProgramId;
		public String mProgTitle;
		public String mResume;
		public String mDescription;
		public String mDuration;
		public long mStartTime;
		public long mEndTime;
		public int mGenre;

		public int mChannelId;
		public int mFreeboxId;
		public int mChannelNumber;
		public String mChannelName;
		public String mImage;
		public long mLastUpdate;
		public boolean mIsFavorite;

		public static ChanProg inflateObject(Cursor c) {
			ChanProg program = new ChanProg();
			program.mProgChannelId = c.getInt(ProgramTable.CONTENT_CHANNEL_ID_COLUMN);
			program.mProgramId = c.getLong(ProgramTable.CONTENT_PROGRAM_ID_COLUMN);
			program.mProgTitle = c.getString(ProgramTable.CONTENT_TITLE_COLUMN);
			program.mResume = c.getString(ProgramTable.CONTENT_RESUME_COLUMN);
			program.mDescription = c.getString(ProgramTable.CONTENT_DESCRIPTION_COLUMN);
			program.mDuration = c.getString(ProgramTable.CONTENT_DURATION_COLUMN);
			program.mStartTime = c.getLong(ProgramTable.CONTENT_STARTTIME);
			program.mEndTime = c.getLong(ProgramTable.CONTENT_ENDTIME);
			program.mGenre = c.getInt(ProgramTable.CONTENT_GENRE);

			program.mChannelId = c.getInt(MARGE + ChannelTable.CONTENT_CHANNEL_ID_COLUMN);
			program.mFreeboxId = c.getInt(MARGE + ChannelTable.CONTENT_CHANNEL_FREEBOX_ID_COLUMN);
			program.mChannelNumber = c.getInt(MARGE + ChannelTable.CONTENT_CHANNEL_NUMBER_COLUMN);
			program.mChannelName = c.getString(MARGE + ChannelTable.CONTENT_CHANNEL_NAME_COLUMN);
			program.mImage = c.getString(MARGE + ChannelTable.CONTENT_CHANNEL_IMAGE_NAME_COLUMN);
			program.mLastUpdate = c.getLong(MARGE + ChannelTable.CONTENT_CHANNEL_LAST_UPDATE_COLUMN);
			program.mIsFavorite = c.getInt(MARGE + ChannelTable.CONTENT_CHANNEL_IS_FAVORITE) == 1;
			return program;
		}
	}
}
